﻿using System;
using System.Text;
using System.Data;
using VIT.DataHelper.DataAccess;
using VIT.Library;
using System.Data.SqlClient;

namespace VIT.Business
{
    public class BLProduct
    {
        SQLSupport sqlSupport = new SQLSupport();

        #region Properties
        public int Id { get; set; }
        public int CategoryId { get; set; }
        public int ManufacturerId { get; set; }
        public int SupplierId { get; set; }
        public string LanguageId { get; set; }
        public string Type { get; set; }
        public string Image { get; set; }
        public string Title { get; set; }
        public string Brief { get; set; }
        public string Description { get; set; }
        public string Tag { get; set; }
        public decimal Price { get; set; }
        public decimal Sale { get; set; }
        public int Views { get; set; }
        public int Orders { get; set; }
        public bool Published { get; set; }
        #endregion

        #region Constructor
        public BLProduct(int id = -1,
                        int categoryId = 0,
                        int manufacturerId = 0,
                        int supplierId = 0,
                        decimal price = 0,
                        decimal sale = 0,
                        int views = 0,
                        string languageId = "",
                        string image = "",
                        string title = "", 
                        string brief = "",
                        string description = "",
                        string tag = "",
                        string type="",
                        int orders = 0,
                        bool published = true)
        {
            this.Id = id;
            this.CategoryId = categoryId;
            this.ManufacturerId = manufacturerId;
            this.SupplierId = supplierId;
            this.LanguageId = languageId;
            this.Type = type;
            this.Image = image;
            this.Title = title;
            this.Brief = brief;
            this.Description = description;
            this.Price = price;
            this.Sale = sale;
            this.Views = views;
            this.Tag = tag;
            this.Orders = orders;
            this.Published = published;
        }
        #endregion

        #region Methods
        public DataTable GetList(int top = 0, int category = 0, int manufacturer = 0, int supplier = 0)
        {
            StringBuilder strQuery = new StringBuilder();
            strQuery.Append("select ");
            if (top!=0)
            {
                strQuery.Append("top ");
                strQuery.Append(top);
            }
            strQuery.Append(" id, Title, Image, Brief, Description, Price, Sale, Tag, Views FROM Products, Products_Language where Id=ProductId and LanguageId=@LanguageId and Published=1 ");
            if (category != 0)
            {
                Provider.Category c = new Provider.Category();
                c.LanguageId = this.LanguageId;
                DataTable dt = c.GetAll();
                string categoryList = dt.GetChildId(category.ToString());
                strQuery.Append("and CategoryId in (" + categoryList + ") ");
            }
            if (manufacturer != 0) strQuery.Append("and ManufacturerId = @ManufacturerId ");
            if (supplier != 0) strQuery.Append("and SupplierId = @SupplierId ");
            strQuery.Append("order by Orders desc");

            SqlParameter ManufacturerId = new SqlParameter("@ManufacturerId", manufacturer);
            SqlParameter SupplierId = new SqlParameter("@SupplierId", supplier);
            SqlParameter LanguageId = new SqlParameter("@LanguageId", this.LanguageId);
            return sqlSupport.ExecuteDataset(false, strQuery.ToString(), LanguageId, ManufacturerId, SupplierId).Tables[0];
        }

        public void GetSingle()
        {
            if (Exist())
            {
                string strQuery = "select CategoryId, Title, ManufacturerId, SupplierId, Image, Brief, Description, Price, Sale, Views, Tag, Type, Orders, Published from Products join Products_Language on Id=ProductId and LanguageId=@LanguageId where Id=@Id and Published=1 ";
                SqlParameter Id = new SqlParameter("@Id", this.Id);
                SqlParameter LanguageId = new SqlParameter("@LanguageId", this.LanguageId);
                DataTable dt = sqlSupport.ExecuteDataset(false, strQuery, Id, LanguageId).Tables[0];

                if (dt.Rows.Count > 0)
                {
                    this.Image = dt.Rows[0]["Image"].ToString();
                    if (!string.IsNullOrEmpty(dt.Rows[0]["Orders"].ToString()))
                        this.Orders = int.Parse(dt.Rows[0]["Orders"].ToString());
                    this.Title = dt.Rows[0]["Title"].ToString();
                    this.Brief = dt.Rows[0]["Brief"].ToString();
                    this.Description = dt.Rows[0]["Description"].ToString();

                    decimal price, sale;
                    int views, manufacturerId, supplierId;
                    decimal.TryParse(dt.Rows[0]["Price"].ToString(), out price);
                    decimal.TryParse(dt.Rows[0]["Sale"].ToString(), out sale);
                    int.TryParse(dt.Rows[0]["Views"].ToString(), out views);
                    int.TryParse(dt.Rows[0]["ManufacturerId"].ToString(), out manufacturerId);
                    int.TryParse(dt.Rows[0]["SupplierId"].ToString(), out supplierId);
                    this.Price = price; this.Sale = sale;
                    this.Views = views; this.ManufacturerId = manufacturerId; this.SupplierId = supplierId;

                    this.Tag = dt.Rows[0]["Tag"].ToString();
                    this.Type = dt.Rows[0]["Type"].ToString();
                    this.CategoryId = Convert.ToInt32(dt.Rows[0]["CategoryId"]);
                    if(!string.IsNullOrEmpty(dt.Rows[0]["Published"].ToString()))
                        this.Published = (bool)dt.Rows[0]["Published"];
                }
            }
            else this.Id = 0;
        }

        private bool Exist()
        {
            string strQuery = "select Id from Products where Id=@Id";
            SqlParameter Id = new SqlParameter("@Id", this.Id);
            return sqlSupport.ExecuteScalar(false, strQuery, Id) != null;
        }

        public DataTable Search(string key)
        {
            string strQuery = "select Id, Title, Image, substring(Brief,0,200) as Brief, Orders, Tag, Published from Article, Article_Language l"
                                + " where Id=ArticleId and LanguageID=@LanguageID and (Title like @vKey or Tag like @vKey) and Published=1 order by Orders, l.DisplayDate DESC";
            SqlParameter LanguageId = new SqlParameter("@LanguageID", this.LanguageId);
            SqlParameter vKey = new SqlParameter("@vKey", "%" + key.Replace(" ", "%") + "%");
            return sqlSupport.ExecuteDataset(false, strQuery, LanguageId, vKey).Tables[0];
        }

        public bool UpdateViewer()
        {
            string sqlCat = "update Products set Views=Views+1 where Id=@Id";
            SqlParameter Id = new SqlParameter("@Id", this.Id);

            return sqlSupport.ExecuteNonQuery(false, sqlCat, Id) > 0;
        }

        public DataTable getTopViewProduct(int top = 0)
        {
            StringBuilder strQuery = new StringBuilder();
            strQuery.Append("select ");
            if (top != 0)
            {
                strQuery.Append("top ");
                strQuery.Append(top);
            }
            strQuery.Append(" id, Title, Image, Brief, Description, Price, Sale, Tag, Views FROM Products, Products_Language where Id=ProductId and LanguageId=@LanguageId and Published=1 ");
            strQuery.Append("order by Views/(DATEDIFF(day,GETDATE()+1,Products_Language.ModifyDate))");

            SqlParameter LanguageId = new SqlParameter("@LanguageId", this.LanguageId);
            return sqlSupport.ExecuteDataset(false, strQuery.ToString(), LanguageId).Tables[0];
        }
        #endregion

    }
}
